from flask import Blueprint, jsonify, request, current_app
from datetime import datetime, timedelta
from models import db, User, Order, UserSegment
from sqlalchemy import func
import pandas as pd
import logging

bp = Blueprint('user_segments', __name__)
logger = logging.getLogger(__name__)

@bp.route('/api/user_segments')
def get_user_segments():
    logger.info("开始获取用户分群数据")
    
    # 获取所有用户
    users = User.query.all()
    total_users = len(users)
    logger.info(f"总用户数: {total_users}")
    
    # 计算各类用户数量
    vip_users = sum(1 for user in users if user.total_consumption >= 10000)
    active_users = sum(1 for user in users if user.is_active)
    churn_users = sum(1 for user in users if user.is_churn)
    potential_users = total_users - vip_users - active_users - churn_users
    
    logger.info(f"用户分布: VIP={vip_users}, 活跃={active_users}, 流失={churn_users}, 潜在={potential_users}")

    # 获取消费趋势数据
    logger.info("开始查询消费趋势数据")
    try:
        trend_data = db.session.query(
            func.date_format(Order.order_date, '%Y-%m-01').label('month'),
            func.sum(Order.total_amount).label('total_amount')
        ).filter(
            Order.status == 'completed'
        ).group_by(
            func.date_format(Order.order_date, '%Y-%m-01')
        ).order_by(
            func.date_format(Order.order_date, '%Y-%m-01')
        ).limit(12).all()
        
        logger.info(f"查询到 {len(trend_data)} 条趋势数据")
        
        # 准备趋势图数据
        trend_labels = []
        trend_values = []
        
        for d in trend_data:
            month_str = d.month
            total_amount = float(d.total_amount) if d.total_amount else 0
            
            trend_labels.append(month_str)
            trend_values.append(round(total_amount, 2))
            logger.info(f"月份: {month_str}, 总金额: {total_amount}")
            
    except Exception as e:
        logger.error(f"查询趋势数据时出错: {str(e)}")
        trend_labels = []
        trend_values = []

    # 获取用户列表（限制100条）
    user_list = []
    for user in users[:100]:
        try:
            last_order = max(user.orders, key=lambda x: x.order_date) if user.orders else None
            user_list.append({
                'id': user.id,
                'username': user.username,
                'membership_level': user.membership_level,
                'total_consumption': float(user.total_consumption),
                'last_checkin': last_order.order_date.strftime('%Y-%m-%d') if last_order else 'N/A',
                'checkin_count': len(user.orders),
                'segment': user.segment
            })
        except Exception as e:
            logger.error(f"处理用户 {user.id} 数据时出错: {str(e)}")
            continue

    response_data = {
        'statistics': {
            'vip': {
                'count': vip_users,
                'percentage': round(vip_users / total_users * 100 if total_users > 0 else 0, 1)
            },
            'potential': {
                'count': potential_users,
                'percentage': round(potential_users / total_users * 100 if total_users > 0 else 0, 1)
            },
            'active': {
                'count': active_users,
                'percentage': round(active_users / total_users * 100 if total_users > 0 else 0, 1)
            },
            'churn': {
                'count': churn_users,
                'percentage': round(churn_users / total_users * 100 if total_users > 0 else 0, 1)
            }
        },
        'charts': {
            'distribution': {
                'vip': vip_users,
                'potential': potential_users,
                'active': active_users,
                'churn': churn_users
            },
            'trend': {
                'labels': trend_labels,
                'values': trend_values
            }
        },
        'users': user_list
    }
    
    logger.info(f"趋势数据: labels={trend_labels}, values={trend_values}")
    return jsonify(response_data)

@bp.route('/api/user_segments/filter', methods=['POST'])
def filter_segments():
    filters = request.json
    logger.info(f"接收到筛选条件: {filters}")
    query = User.query

    # 应用日期范围筛选
    if filters.get('dateRange'):
        start_date, end_date = filters['dateRange'].split(' - ')
        start_date = datetime.strptime(start_date, '%Y-%m-%d')
        end_date = datetime.strptime(end_date, '%Y-%m-%d')
        query = query.join(Order).filter(
            Order.order_date.between(start_date, end_date)
        ).group_by(User.id)

    # 应用消费金额筛选
    if filters.get('amountRange'):
        subquery = db.session.query(
            Order.user_id,
            func.sum(Order.total_amount).label('total_amount')
        ).filter(Order.status == 'completed').group_by(Order.user_id).subquery()
        
        amount_range = filters['amountRange']
        if amount_range == '0-1000':
            query = query.join(subquery, User.id == subquery.c.user_id).filter(
                subquery.c.total_amount.between(0, 1000)
            )
        elif amount_range == '1000-5000':
            query = query.join(subquery, User.id == subquery.c.user_id).filter(
                subquery.c.total_amount.between(1000, 5000)
            )
        elif amount_range == '5000+':
            query = query.join(subquery, User.id == subquery.c.user_id).filter(
                subquery.c.total_amount >= 5000
            )

    # 应用会员等级筛选
    if filters.get('membershipLevel'):
        query = query.filter(User.membership_level == filters['membershipLevel'])

    # 获取筛选后的用户
    filtered_users = query.all()
    total_filtered = len(filtered_users)
    logger.info(f"筛选后的用户数量: {total_filtered}")
    
    # 重新计算统计数据
    vip_users = sum(1 for user in filtered_users if user.total_consumption >= 10000)
    active_users = sum(1 for user in filtered_users if user.is_active)
    churn_users = sum(1 for user in filtered_users if user.is_churn)
    potential_users = total_filtered - vip_users - active_users - churn_users

    # 获取消费趋势数据
    logger.info("开始查询消费趋势数据")
    try:
        trend_query = db.session.query(
            func.date_format(Order.order_date, '%Y-%m-01').label('month'),
            func.sum(Order.total_amount).label('total_amount')
        ).filter(Order.status == 'completed')
        
        # 如果有日期范围筛选，应用到趋势查询
        if filters.get('dateRange'):
            trend_query = trend_query.filter(Order.order_date.between(start_date, end_date))
        
        # 如果有用户筛选，只计算这些用户的趋势
        if filtered_users:
            user_ids = [user.id for user in filtered_users]
            trend_query = trend_query.filter(Order.user_id.in_(user_ids))
        
        # 分组并排序
        trend_data = trend_query.group_by(
            func.date_format(Order.order_date, '%Y-%m-01')
        ).order_by(
            func.date_format(Order.order_date, '%Y-%m-01')
        ).all()
        
        logger.info(f"查询到 {len(trend_data)} 条趋势数据")
        
        # 准备趋势图数据
        trend_labels = []
        trend_values = []
        
        for d in trend_data:
            month_str = d.month
            total_amount = float(d.total_amount) if d.total_amount else 0
            trend_labels.append(month_str)
            trend_values.append(round(total_amount, 2))
            logger.info(f"月份: {month_str}, 总金额: {total_amount}")
            
    except Exception as e:
        logger.error(f"查询趋势数据时出错: {str(e)}")
        trend_labels = []
        trend_values = []

    # 转换用户数据
    user_list = []
    for user in filtered_users[:100]:  # 限制返回前100条记录
        last_order = max(user.orders, key=lambda x: x.order_date) if user.orders else None
        user_list.append({
            'id': user.id,
            'username': user.username,
            'membership_level': user.membership_level,
            'total_consumption': float(user.total_consumption),
            'last_checkin': last_order.order_date.strftime('%Y-%m-%d') if last_order else 'N/A',
            'checkin_count': len(user.orders),
            'segment': user.segment
        })

    response_data = {
        'statistics': {
            'vip': {
                'count': vip_users,
                'percentage': round(vip_users / total_filtered * 100 if total_filtered > 0 else 0, 1)
            },
            'potential': {
                'count': potential_users,
                'percentage': round(potential_users / total_filtered * 100 if total_filtered > 0 else 0, 1)
            },
            'active': {
                'count': active_users,
                'percentage': round(active_users / total_filtered * 100 if total_filtered > 0 else 0, 1)
            },
            'churn': {
                'count': churn_users,
                'percentage': round(churn_users / total_filtered * 100 if total_filtered > 0 else 0, 1)
            }
        },
        'charts': {
            'distribution': {
                'vip': vip_users,
                'potential': potential_users,
                'active': active_users,
                'churn': churn_users
            },
            'trend': {
                'labels': trend_labels,
                'values': trend_values
            }
        },
        'users': user_list
    }
    
    logger.info(f"趋势数据: labels={trend_labels}, values={trend_values}")
    return jsonify(response_data)

@bp.route('/api/segments', methods=['POST'])
def create_segment():
    try:
        data = request.get_json()
        logger.info(f"收到创建分群请求: {data}")
        
        if not data or 'name' not in data or 'conditions' not in data:
            logger.error("请求数据缺少必要字段")
            return jsonify({'error': '缺少必要字段'}), 400
            
        # 创建新分群
        segment = UserSegment(
            name=data['name'],
            description=data.get('description', ''),
            conditions=data['conditions']
        )
        
        logger.info(f"创建分群: name={segment.name}, conditions={segment.conditions}")
        
        # 获取所有用户
        users = User.query.all()
        logger.info(f"开始处理 {len(users)} 个用户")
        
        # 应用条件筛选用户
        matched_users = []
        for user in users:
            try:
                if segment.apply_conditions(user):
                    matched_users.append(user)
            except Exception as e:
                logger.error(f"处理用户 {user.id} 时出错: {str(e)}")
                continue
                
        logger.info(f"找到 {len(matched_users)} 个匹配的���户")
        
        # 保存分群
        db.session.add(segment)
        db.session.commit()
        
        return jsonify({
            'id': segment.id,
            'name': segment.name,
            'matched_users': len(matched_users)
        }), 201
        
    except Exception as e:
        logger.error(f"创建分群时出错: {str(e)}")
        db.session.rollback()
        return jsonify({'error': str(e)}), 500

@bp.route('/api/segments/<int:segment_id>', methods=['GET'])
def get_segment(segment_id):
    try:
        segment = UserSegment.query.get_or_404(segment_id)
        logger.info(f"获取分群信息: id={segment_id}")
        
        # 获取匹配的用户
        users = User.query.all()
        matched_users = [user for user in users if segment.apply_conditions(user)]
        
        return jsonify({
            'id': segment.id,
            'name': segment.name,
            'description': segment.description,
            'conditions': segment.conditions,
            'matched_users': len(matched_users),
            'created_at': segment.created_at.isoformat(),
            'updated_at': segment.updated_at.isoformat()
        })
        
    except Exception as e:
        logger.error(f"获取分群信息时出错: {str(e)}")
        return jsonify({'error': str(e)}), 500

@bp.route('/api/segments/<int:segment_id>', methods=['PUT'])
def update_segment(segment_id):
    try:
        segment = UserSegment.query.get_or_404(segment_id)
        data = request.get_json()
        logger.info(f"更新分群: id={segment_id}, data={data}")
        
        if 'name' in data:
            segment.name = data['name']
        if 'description' in data:
            segment.description = data['description']
        if 'conditions' in data:
            segment.conditions = data['conditions']
            
        db.session.commit()
        logger.info(f"分群更新成功: id={segment_id}")
        
        return jsonify({
            'id': segment.id,
            'name': segment.name,
            'description': segment.description,
            'conditions': segment.conditions
        })
        
    except Exception as e:
        logger.error(f"更新分群时出错: {str(e)}")
        db.session.rollback()
        return jsonify({'error': str(e)}), 500

@bp.route('/api/segments/<int:segment_id>', methods=['DELETE'])
def delete_segment(segment_id):
    try:
        segment = UserSegment.query.get_or_404(segment_id)
        logger.info(f"删除分群: id={segment_id}")
        
        db.session.delete(segment)
        db.session.commit()
        
        return '', 204
        
    except Exception as e:
        logger.error(f"删除分群时出错: {str(e)}")
        db.session.rollback()
        return jsonify({'error': str(e)}), 500

def load_user_data():
    # 读取用户数据
    users_df = pd.read_csv('test_users.csv')
    scores_df = pd.read_csv('consumption_scores.csv')
    return users_df, scores_df

@bp.route('/api/user_segments/get_user_by_tag/<tag>')
def get_user_by_tag(tag):
    users_df, scores_df = load_user_data()
    
    # 获取指定标签的用户数据
    user_data = users_df[users_df['tag'] == tag].to_dict('records')
    user_score = scores_df[scores_df['tag'] == tag].to_dict('records')[0]
    
    if not user_data:
        return jsonify({'error': '未找到该标签的用户'}), 404
    
    # 构建响应数据
    response = {
        'user_info': {
            'name': user_data[0]['name'],
            'gender': user_data[0]['gender'],
            'age': user_data[0]['age'],
            'phone': user_data[0]['phone'],
            'email': user_data[0]['email'],
            'consumption_preference': user_data[0]['consumption_preference'],
            'membership_level': user_data[0]['membership_level'],
            'tag': user_data[0]['tag']
        },
        'consumption_records': user_data,
        'consumption_score': user_score
    }
    
    return jsonify(response)

@bp.route('/api/user_segments/get_all_tags')
def get_all_tags():
    users_df, _ = load_user_data()
    tags = users_df['tag'].unique().tolist()
    return jsonify({'tags': tags})
